﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace UdrinkWater_Project.Officer
{
    public partial class MaterialLoan : System.Web.UI.Page
    {

        string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        DateTime OrderM_ID = DateTime.Now;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string T1 = OrderM_ID.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH"));
                string T2 = OrderM_ID.ToString("HH:mm:ss", new System.Globalization.CultureInfo("en-US"));
                txtOrderDate.Text = T1 + " " + T2;
                chkRow.Value = "0";
                Session["dtTemp"] = null;
            }
            else
            {

            }
        }

        protected void ASPxGridView4_RowCommand(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewRowCommandEventArgs e)
        {
            DataTable DataPro = new DataTable();
            if (e.CommandArgs.CommandName == "Cmd_Select")
            {
                string Id = e.CommandArgs.CommandArgument.ToString();
                DataPro = GetDetialPro(Id);
                txtProductId.Text = DataPro.Rows[0]["Product_ID"].ToString();
                txtProductName.Text = DataPro.Rows[0]["Product_name"].ToString();
                txtUnit.Text = DataPro.Rows[0]["Unit_Name"].ToString();
                hdUnit.Value = DataPro.Rows[0]["Unit"].ToString();
                float price = float.Parse(DataPro.Rows[0]["Price"].ToString());
                txtUnitPrice.Text = price.ToString("0.00");
                txtCount.Text = "0";
                txtCount.Focus();
            }
        }

        private DataTable GetDetialPro(string Product_ID)
        {
            string sqltext = @"SELECT   Product_ID
                                        ,Product_name
                                        ,Unit
                                        ,TB_Unit_Category.Unit_Name
                                        ,Price
                                        FROM TB_Product
                                        inner join TB_Unit_Category
                                        on TB_Product.Unit = TB_Unit_Category.Unit_ID
                                        WHERE Product_ID ='" + Product_ID + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }

        protected void btAddList_Click(object sender, EventArgs e)
        {
            DataTable dtList = new DataTable();
            if (chkRow.Value == "0")
            {
                dtList.Columns.Add("MaterialLoan_ID", typeof(DateTime));
                dtList.Columns.Add("Product_ID", typeof(string));
                dtList.Columns.Add("Count", typeof(int));
                dtList.Columns.Add("Price", typeof(float));
                dtList.Columns.Add("Total", typeof(float));
                dtList.Columns.Add("Unit", typeof(string));
                chkRow.Value = "1";
                lbWranList.Visible = false;
            }

            else if (chkRow.Value != "0")
            {
                dtList = (DataTable)Session["dtTemp"];
                for (int i = 0; i < dtList.Rows.Count; i++)
                {
                    if (dtList.Rows[i]["Product_ID"].ToString() == txtProductId.Text)
                    {
                        lbWranList.Visible = true;
                        break;
                    }
                    else
                        lbWranList.Visible = false;

                }

            }

            if (lbWranList.Visible == false)
            {

                DataRow dtRow = dtList.NewRow();
                dtRow["MaterialLoan_ID"] = Convert.ToDateTime(txtOrderDate.Text);
                dtRow["Product_ID"] = txtProductId.Text;
                dtRow["Count"] = txtCount.Text;
                dtRow["Price"] = txtUnitPrice.Text;
                dtRow["Unit"] = hdUnit.Value;
                dtRow["Total"] = Convert.ToDouble(txtCount.Text) * Convert.ToDouble(txtUnitPrice.Text);
                dtList.Rows.Add(dtRow);
                Session["dtTemp"] = dtList;
                ASPxdtail.DataSource = dtList;
                ASPxdtail.DataBind();
            }
        }

        protected void ASPxdtail_RowCommand(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewRowCommandEventArgs e)
        {
            DataTable dtList = new DataTable();
            dtList = (DataTable)Session["dtTemp"];
            DataRow[] dtRow;
            string xxx = e.CommandArgs.CommandArgument.ToString();
            dtRow = dtList.Select("Product_ID = '" + xxx + "'");
            foreach (DataRow r in dtRow)
            {
                r.Delete();
            }
            Session["dtTemp"] = dtList;
            dtList.AcceptChanges();
            ASPxdtail.DataSource = dtList;
            ASPxdtail.DataBind();
            lbWranList.Visible = false;
        }

        protected void btNewOrder_Click(object sender, EventArgs e)
        {
            Response.Redirect("~/Admin/MaterialLoan.aspx");
        }

        protected void btSaveAndReport_Click(object sender, EventArgs e)
        {
            DataTable dtList = new DataTable();
            dtList = (DataTable)Session["dtTemp"];
            if (Session["dtTemp"] != null)
            {
                if (dtList.Rows.Count > 0)//Session["dtTemp"] != null)
                {
                dtList.Columns.Remove("Total");
                dtList.Columns.Remove("Unit");
                MaterialLoanDataContext db = new MaterialLoanDataContext();
                TB_MaterialLoan x = new TB_MaterialLoan();
                x.MaterialLoan_ID = Convert.ToDateTime(txtOrderDate.Text);
                x.Address = txtAdd0.Text;
                x.Province_ID = int.Parse(dlPro.SelectedValue);
                x.Aumphur_ID = int.Parse(dlAmp.SelectedValue);
                x.District_ID = int.Parse(dlDis.SelectedValue);
                x.FirstName = txtFrist.Text;
                x.LastName = txtLast.Text;
                x.Employee_ID = ddlEmp.SelectedValue;
                x.Status = int.Parse(ddlStatus.SelectedValue);
                db.TB_MaterialLoans.InsertOnSubmit(x);
                db.SubmitChanges();
                SqlConnection destinationConnection = new SqlConnection(Connection);
                destinationConnection.Open();
                SqlBulkCopy bulkcopy = new SqlBulkCopy(Connection);
                //I assume you have created the table previously
                //Someone else here already showed how  
                bulkcopy.DestinationTableName = "dbo.TB_MaterialLoan_List";
                try
                {
                    bulkcopy.WriteToServer(dtList);
                    Response.Write(@"<script language='javascript'>alert('ทำรายการเรียบร้อยแล้ว\n');</script>");
                }
                catch (Exception ex)
                {

                }
                }
                else
                {
                    Response.Write(@"<script language='javascript'>alert('ไม่ได้ทำรายการ\n');</script>");
                }
            }
            else
            {
                Response.Write(@"<script language='javascript'>alert('ไม่ได้ทำรายการ\n');</script>");
            }
        }

        protected void ASPxGridView1_RowCommand(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewRowCommandEventArgs e)
        {
            MaterialLoanDataContext db = new MaterialLoanDataContext();
            string xOrderM_Date = e.CommandArgs.CommandArgument.ToString();
            var q = (from p in db.TB_MaterialLoans
                     where p.MaterialLoan_ID == Convert.ToDateTime(xOrderM_Date)
                     select p).First();
            txtOrderDate.Text = q.MaterialLoan_ID.ToString();
            txtAdd0.Text = q.Address.ToString();
            txtFrist.Text = q.FirstName.ToString();
            txtLast.Text = q.LastName.ToString();
            ddlEmp.SelectedValue = q.Employee_ID.ToString();
            ddlStatus.SelectedValue = q.Status.ToString();

            Session["dtTemp"] = GetMDetial(q.MaterialLoan_ID);
            ASPxdtail.DataSource = Session["dtTemp"];
            ASPxdtail.DataBind();
        }

        private DataTable GetMDetial(DateTime OrderM_Date)
        {
            string sOrderM_Date = OrderM_Date.ToString("yyyy-MM-dd HH:mm:ss", new System.Globalization.CultureInfo("en-US"));
            string sqltext = @"SELECT MaterialLoan_ID
                                ,TB_MaterialLoan_List.Product_ID
                                ,TB_Product.Product_name
                                ,TB_Product.Unit
                                ,TB_Product.Product_name
                                ,[Count]
                                ,TB_MaterialLoan_List.[Price]
                                ,TB_MaterialLoan_List.[Price] * TB_MaterialLoan_List.[Count] As Total
                                FROM TB_MaterialLoan_List
                                inner join TB_Product on TB_MaterialLoan_List.Product_ID = TB_Product.Product_ID
                                inner join TB_Unit_Category on TB_Product.Unit = TB_Unit_Category.Unit_ID
                                WHERE MaterialLoan_ID ='" + sOrderM_Date + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
    }
}